CREATE VIEW VTestSummary
AS

SELECT TestGroupName, HostName, NumberOfThreads, Sync, TestType, MinAT, MaxAT, AvgAT 
FROM 
  (SELECT DISTINCT R1.ID, R1.Name AS TestGroupName, R2.Name AS HostName
  FROM
    (SELECT T1.ID, HostId, Name FROM TestGroup_Host AS T1
    INNER JOIN TestGroup AS T2 ON T1.TestGroupId=T2.ID
    )
  AS R1
  INNER JOIN
    (SELECT HostId, Name FROM TestGroup_Host AS T1
    INNER JOIN Host AS T2 ON T1.HostId=T2.ID
    )
  AS R2
  ON R1.HostId=R2.HostId
  )
AS GroupHost
LEFT JOIN
  (SELECT TestGroup_Host_id AS ID, NumberOfThreads, Name AS Sync, TestType, MIN(AccessTime) / 1e6 AS MinAT, MAX(AccessTime) / 1e6 AS MaxAT, AVG(AccessTime) / 1e6 AS AvgAT
  FROM Results
  INNER JOIN
	(SELECT Test.ID, NumberOfThreads, Name, TestType
	FROM Test
	LEFT JOIN SyncMechanism ON SyncMechanism.ID=Test.SyncId
	) 
  AS T1
  ON T1.ID=Results.TestId
  GROUP BY TestGroup_Host_id, TestId, NumberOfThreads, Name, TestType
  )
AS TestSummary
ON GroupHost.ID=TestSummary.ID

GO
